﻿using Microsoft.Extensions.DependencyInjection;
using Modules.CheckIn.Entitys;
using Modules.System.User;
using MySql.Data.MySqlClient;
using PetaPoco;
using PmSoft;
using PmSoft.ApplicationContext;
using PmSoft.DBContext;
using PmSoft.Entity;
using PmSoft.Exceptions;
using PmSoft.Repositories;
using PmSoft.Utilities;
using System.Drawing.Printing;

namespace Modules.CheckIn.Repositories
{
    [UseDI(ServiceLifetime.Scoped)]
    public class CheckInDetailRepository : Repository<CheckInDetail, CheckinDbContext>
    {
        private readonly IApplicationContext applicationContext;
        private readonly IDatabase dbContext;
        private static List<string> checkinDetailTables = new List<string>();//广告点击记录分表表名集合
        private static readonly object locker = new object();
        private static DateTime? lastUpdateTable;//表名称列表最后更新时间

        public CheckInDetailRepository(CheckinDbContext dbContext, IApplicationContext applicationContext) : base(dbContext, applicationContext)
        {
            this.dbContext = dbContext;
            this.applicationContext = applicationContext;
        }

        #region 私有方法

        /// <summary>
        /// 获取所有考勤明细表名
        /// </summary>
        private void LoadTableNames()
        {
            lock (locker)
            {
                //30秒更新一次
                if (lastUpdateTable.HasValue && DateTime.Now.Subtract(lastUpdateTable.Value).TotalSeconds < 30)
                    return;

                Sql sql = Sql.Builder
                    .Append("SELECT table_name FROM information_schema.TABLES WHERE table_name LIKE 'checkin_detail_%'  ORDER BY table_name ASC LIMIT 100;");

                checkinDetailTables = dbContext.Fetch<string>(sql);
                lastUpdateTable = DateTime.Now;
            }
        }

        /// <summary>
        /// 是否存在指定月份表
        /// </summary>
        /// <param name="month">月份</param>
        /// <returns></returns>
        private bool ExistsTableName(int month)
        {
            LoadTableNames();
            if (checkinDetailTables.Contains(GetTableName(month), StringComparer.OrdinalIgnoreCase))
                return true;
            return false;
        }

        private string GetTableName(int month)
        {
            return $"checkin_detail_{month}";
        }

        #endregion 私有方法

        public IPagedList<CheckInDetail> GetCheckInDetailMonths(CheckInDetailQuery query, int pageIndex, int pageSize)
        {
            string tableName = GetClickTableName(query.StartDate, query.EndDate);
            Sql sql = Sql.Builder.Select("T.*").From(tableName);
            if (query.ProjectId.HasValue)
                sql.Where("T.project_id=@0", query.ProjectId);
            if (query.GroupId.HasValue)
                sql.Where("T.group_id=@0", query.GroupId);
            if (query.EntryExitMark.HasValue)
                sql.Where("T.entry_exit_mark=@0", query.EntryExitMark);
            if (query.StartDate.HasValue)
                sql.Where("T.create_time >=@0", query.StartDate.Value.Date);
            if (query.EndDate.HasValue)
                sql.Where("T.create_time <@0", query.EndDate.Value.AddDays(1).Date);
            if (!string.IsNullOrEmpty(query.Keywords))
            {
                if (query.personIds != null && query.personIds.Count() > 0)
                {
                    sql.Where("T.person_id in (@0)", query.personIds);
                }
                else
                {
                    sql.Where("T.person_id=-1");
                }
            }
            sql.OrderBy("T.create_time DESC");
            return GetDefPagingEntities(pageSize, pageIndex, sql);
        }

        public IPagedList<CheckInDetail> GetCheckInDetailsByMonth(CheckInDetailMonthQuery query, int pageIndex, int pageSize)
        {
            if (!query.Month.HasValue || !ExistsTableName(query.Month.Value))
                return new PagedList<CheckInDetail>(Enumerable.Empty<CheckInDetail>(), 1, 10, 0);

            try
            {
                Sql sql = Sql.Builder.Select("*");
                sql.From(GetTableName(query.Month.Value));
                if (!string.IsNullOrEmpty(query.Keywords))
                {
                    if (query.personIds != null && query.personIds.Count() > 0)
                    {
                        sql.Where("person_id in (@0)", query.personIds);
                    }
                    else
                    {
                        sql.Where("person_id=-1");
                    }
                }
                if (query.ProjectId.HasValue)
                    sql.Where("project_id=@0", query.ProjectId.Value);
                sql.OrderBy("id");
                var result = GetDefPagingEntities(pageSize, pageIndex, sql);
                return result;
            }
            catch (MySqlException ex)
            {
                return new PagedList<CheckInDetail>(new List<CheckInDetail>(), 1, 10, 0);
            }
        }

        public IPagedList<CheckInDetail> GetCheckInDetailsByPersonGroup(CheckInDetailMonthQuery query, int pageIndex, int pageSize)
        {
            if (!query.Month.HasValue || !ExistsTableName(query.Month.Value))
                return new PagedList<CheckInDetail>(Enumerable.Empty<CheckInDetail>(), 1, 10, 0);
            try
            {
                Sql sql = Sql.Builder.Select(" project_id, MONTH, member_id, person_id, group_id ").From(GetTableName(query.Month.Value)).Where("checkin_date<= date_sub(curdate(), interval 1 day)");
                if (!string.IsNullOrEmpty(query.Keywords))
                {
                    if (query.personIds != null && query.personIds.Count() > 0)
                    {
                        sql.Where("person_id in (@0)", query.personIds);
                    }
                    else
                    {
                        sql.Where("person_id=-1");
                    }
                }
                if (query.ProjectId.HasValue)
                    sql.Where("project_id=@0", query.ProjectId.Value);
                sql.GroupBy("project_id, MONTH, member_id, person_id, group_id");
                sql.OrderBy("person_id");
                var result = GetDefPagingEntities(pageSize, pageIndex, sql);
                return result;
            }
            catch (MySqlException ex)
            {
                return new PagedList<CheckInDetail>(new List<CheckInDetail>(), 1, 10, 0);
            }
        }

        private string GetClickTableName(DateTime? startDate, DateTime? endDate)
        {
            LoadTableNames();
            string format = "yyyyMM";//换算成年月格式
            if (!startDate.HasValue && !endDate.HasValue)
            {
                startDate = DateTime.Now.Date;
                endDate = DateTime.Now.Date;
            }
            string tableName = "checkin_detail_" + startDate.Value.ToString(format);
            tableName = checkinDetailTables.Contains(tableName) ? tableName : checkinDetailTables.FirstOrDefault();

            string tableSql = "(SELECT * FROM " + tableName;
            DateTime dt = startDate.Value.AddMonths(1);
            while (dt.ToString(format) != endDate.Value.ToString(format) && dt < endDate.Value)
            {
                tableName = "checkin_detail_" + dt.ToString(format);
                dt = dt.AddMonths(1);
                if (!checkinDetailTables.Contains(tableName))
                    continue;
                tableSql += " UNION ALL SELECT * FROM " + tableName;
            }
            //判断存在跨月的，如9.30--10.11的情况，此处dt的值为11.30大于了10.11,则结束时间也加上1个月
            if (dt < endDate.Value || (dt.Day > endDate.Value.Day && dt < endDate.Value.AddMonths(1)))
            {
                tableName = "checkin_detail_" + dt.ToString(format);
                if (checkinDetailTables.Contains(tableName) && tableName != checkinDetailTables.FirstOrDefault())
                    tableSql += " UNION ALL SELECT * FROM " + tableName;
            }
            tableSql += ") as T";
            return tableSql;
        }

        public CheckInDetail? GetCheckInDetail(string id, int month)
        {
            if (!ExistsTableName(month))
                return null;

            Sql sql = Sql.Builder.Select("*").From(GetTableName(month)).Where("id=@0", id);
            return dbContext.FirstOrDefault<CheckInDetail>(sql);
        }

        public IEnumerable<int> GetCheckInDetailPersonMonthForm(int memberId, int month)
        {
            if (!ExistsTableName(month))
                return Enumerable.Empty<int>();

            Sql sql = Sql.Builder;
            sql.Select("dayofmonth(checkin_date) AS days")
                .From(GetTableName(month))
                .Where("checkin_date<= date_sub(curdate(), interval 1 day)");
            sql.Where("member_id=@0", memberId);
            sql.GroupBy("checkin_date");
            sql.OrderBy("checkin_date");
            return DbContext.Fetch<int>(sql);
        }

        public int GetCheckInDetailCount(ProjectCheckinCountQuery query)
        {
            if (!ExistsTableName(query.Month))
                return 0;

            Sql sql = Sql.Builder;
            sql.Select("COUNT(DISTINCT person_id)")
                .From(GetTableName(query.Month));
            if (query.ProjectId.HasValue)
                sql.Where("project_id =@0", query.ProjectId.Value);
            if (!string.IsNullOrEmpty(query.GroupIds))
                sql.Where("group_id  in(@0)", ValueUtility.ConvertToList<int>(query.GroupIds));
            return DbContext.ExecuteScalar<int>(sql);
        }

        /// <summary>
        /// 根据班组查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<PersonCount> GetPersonCountsByProjectId(ProjectCheckinCountQuery query)
        {
            if (!ExistsTableName(query.Month))
                return Enumerable.Empty<PersonCount>();

            Sql sql = Sql.Builder;
            sql.Select("COUNT(DISTINCT person_id) Count,group_id ")
                .From(GetTableName(query.Month));
            if (query.ProjectId.HasValue)
                sql.Where("project_id =@0", query.ProjectId.Value);
            if (!string.IsNullOrEmpty(query.GroupIds))
                sql.Where("group_id  in(@0)", ValueUtility.ConvertToList<int>(query.GroupIds));
            sql.GroupBy("group_id");
            return DbContext.Fetch<PersonCount>(sql);
        }

        /// <summary>
        /// 根据班组和人员查询分组
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<PersonCount> GetPersonCountsByProjectIdGroupPersonAndGroupId(ProjectCheckinCountQuery query)
        {
            if (!ExistsTableName(query.Month))
                return Enumerable.Empty<PersonCount>();
            Sql sql = Sql.Builder;
            sql.Select(" count(DISTINCT  person_id,checkin_date) as Count,person_id,group_id ")
                .From(GetTableName(query.Month));
            if (query.ProjectId.HasValue)
                sql.Where("project_id =@0", query.ProjectId.Value);
            sql.GroupBy("person_id,group_id");
            return DbContext.Fetch<PersonCount>(sql);
        }

        public IEnumerable<CheckInDetail> GetCheckInDetailsByProjectIdAndMonth(int projectId, int month)
        {
            if (!ExistsTableName(month))
                return Enumerable.Empty<CheckInDetail>();

            Sql sql = Sql.Builder;
            sql.Select("*")
                .From(GetTableName(month));
            sql.Where("project_id =@0", projectId);
            sql.OrderBy("create_time desc");
            return DbContext.Fetch<CheckInDetail>(sql);
        }

        public IEnumerable<CheckInDetail> GetCheckInDetailsByMonthAndCheckDateAndMemberId(DateTime date, int memberId)
        {
            if (!ExistsTableName(date.ToMonthNumber()))
                return Enumerable.Empty<CheckInDetail>();

            Sql sql = Sql.Builder;
            sql.Select("*")
                .From(GetTableName(date.ToMonthNumber()));
            sql.Where("DATEDIFF(checkin_date,@0)=0", date);
            sql.Where("member_id = @0", memberId);
            sql.OrderBy("create_time desc");
            return DbContext.Fetch<CheckInDetail>(sql);
        }

        internal IEnumerable<CheckInDetail> GetCheckinDetailsByMonthAndMemberId(int month, int memberId)
        {
            if (!ExistsTableName(month))
                return Enumerable.Empty<CheckInDetail>();

            Sql sql = Sql.Builder;
            sql.Select("*")
                .From(GetTableName(month));
            sql.Where("member_id = @0", memberId);
            sql.OrderBy("create_time desc");
            return DbContext.Fetch<CheckInDetail>(sql);
        }

        public IEnumerable<CheckInDetail> GetCheckinDetailsByMonthAndMemberIds(int month, IEnumerable<int> memberIds)
        {
            if (memberIds == null || memberIds.Count() == 0 || !ExistsTableName(month))
                return Enumerable.Empty<CheckInDetail>();

            Sql sql = Sql.Builder;
            sql.Select("*")
                .From(GetTableName(month));
            sql.Where("member_id in(@0)", memberIds);
            sql.OrderBy("create_time desc");
            return DbContext.Fetch<CheckInDetail>(sql);
        }
    }
}